Public Class UtilsDB

    Private Shared m_PacientesAdapter As New WeiDaoDataSetTableAdapters.PacientesTableAdapter
    Private Shared m_AgendaAdapter As New WeiDaoDataSetTableAdapters.AgendaTableAdapter
    Private Shared m_ConsAgendaAdapter As New WeiDaoDataSetTableAdapters.ConsAgendaTableAdapter
    Private Shared m_Pacientes As New WeiDaoDataSet.PacientesDataTable
    Private Shared m_Agenda As New WeiDaoDataSet.AgendaDataTable
    Private Shared m_ConsAgenda As New WeiDaoDataSet.ConsAgendaDataTable

    Private Shared m_Conneccion As OleDb.OleDbConnection


    Shared Sub New()

        m_Agenda = GetFreshAgenda()
        '    m_ConsAgenda.Compute()
        m_Conneccion = m_AgendaAdapter.Connection

    End Sub


    Shared Function GetFreshConsAgenda() As WeiDaoDataSet.ConsAgendaDataTable
        '    'Devuelve la consula de agenda cargada desde la bd

        m_ConsAgendaAdapter.ClearBeforeFill = True
        m_ConsAgenda = m_ConsAgendaAdapter.GetData
        Return m_ConsAgenda

    End Function
    Shared Function GetFreshPacientes() As WeiDaoDataSet.PacientesDataTable
        'Devuelve la tabla pacientes cargada desde la bd

        m_PacientesAdapter.ClearBeforeFill = True
        m_Pacientes = m_PacientesAdapter.GetData()
        Return m_Pacientes

    End Function
    Shared Function GetFreshAgenda() As WeiDaoDataSet.AgendaDataTable

        m_AgendaAdapter.ClearBeforeFill = True
        m_Agenda = m_AgendaAdapter.GetData
        Return m_Agenda

    End Function
    Shared Function GetPaciente(ByVal idPac As Short) As WeiDaoDataSet.PacientesRow

        If m_Pacientes.Rows.Count = 0 Then
            m_Pacientes = m_PacientesAdapter.GetData
        End If

        Return m_Pacientes.FindByIDPaciente(idPac)

    End Function
    Shared Function GetCita(ByVal idAG As Short) As WeiDaoDataSet.ConsAgendaRow

        If m_ConsAgenda.Rows.Count = 0 Then
            m_ConsAgenda = m_ConsAgendaAdapter.GetData
        End If

        Return m_ConsAgenda.FindByIdAgenda(idAG)

    End Function

    Shared Sub AddPaciente(ByVal sNombre As String)

        Dim oPac As WeiDaoDataSet.PacientesRow
        oPac = m_Pacientes.NewPacientesRow

        oPac.Nombre = sNombre
        m_Pacientes.AddPacientesRow(oPac)

        m_PacientesAdapter.Update(m_Pacientes)

        oPac.AcceptChanges()
        m_Pacientes.AcceptChanges()

    End Sub
    Shared Sub AddAgenda(ByVal Fe As Date, ByVal Durac As Short, ByVal NPa As Short)

        Dim oAge As WeiDaoDataSet.AgendaRow
        oAge = m_Agenda.NewAgendaRow
        m_Agenda.AddAgendaRow(Fe, Durac, False, NPa)
        m_AgendaAdapter.Update(m_Agenda)
        m_Agenda.AcceptChanges()

    End Sub
    Shared Sub EditAgenda(ByVal cita As WeiDaoDataSet.ConsAgendaRow, ByVal Fe As Date, ByVal Durac As Short, ByVal NPa As Short)

        ' MsgBox(cita.RowState.ToString)
        Dim oAge As WeiDaoDataSet.AgendaRow
        oAge = m_Agenda.FindByIdAgenda(cita.IdAgenda)
        With oAge
            .Fecha = Fe
            .Durac = Durac
            .NPaciente = NPa
        End With

        m_AgendaAdapter.Update(oAge)

    End Sub

    Shared Function GetCitas(ByVal fInicio As Date, ByVal fFin As Date) As WeiDaoDataSet.ConsAgendaDataTable
        Dim oRet As WeiDaoDataSet.ConsAgendaDataTable
        Dim oReader As OleDb.OleDbDataReader
        Dim oCommand As OleDb.OleDbCommand
        Dim bClosed As Boolean = False
        Dim sQueryText As String = "Select * FROM ConsAgenda WHERE Fecha > #{0}# AND Fecha <#{1}#"

        sQueryText = String.Format(sQueryText, fInicio.ToString("MM/dd/yyyy"), fFin.ToString("MM/dd/yyyy"))

        oCommand = New OleDb.OleDbCommand(sQueryText, m_Conneccion)

        If (m_Conneccion IsNot Nothing) AndAlso (m_Conneccion.State = ConnectionState.Closed) Then
            m_Conneccion.Open()
            bClosed = True
        End If

        oReader = oCommand.ExecuteReader

        oRet = New WeiDaoDataSet.ConsAgendaDataTable
        oRet.Load(oReader)

        If bClosed Then m_Conneccion.Close()

        Return oRet
    End Function
    Shared Sub EliminaCita(ByVal cita As WeiDaoDataSet.ConsAgendaRow)

        Dim oAge As WeiDaoDataSet.AgendaRow
        oAge = m_Agenda.FindByIdAgenda(cita.IdAgenda)
        m_AgendaAdapter.Delete(oAge.IdAgenda, oAge.Fecha, oAge.Durac, oAge.Falto, oAge.NPaciente)

    End Sub
    Friend Shared Function GetUltimaCita(ByVal Npac As Integer) As MiResultado
        Dim oRet As WeiDaoDataSet.AgendaDataTable
        Dim oReader As OleDb.OleDbDataReader
        Dim oCommand As OleDb.OleDbCommand
        Dim bClosed As Boolean = False
        Dim sQueryText As String = "Select * FROM Agenda WHERE NPaciente = {0} ORDER BY Agenda.Fecha DESC;"

        sQueryText = String.Format(sQueryText, Npac.ToString)
        oCommand = New OleDb.OleDbCommand(sQueryText, m_Conneccion)

        If (m_Conneccion IsNot Nothing) AndAlso (m_Conneccion.State = ConnectionState.Closed) Then
            m_Conneccion.Open()
            bClosed = True
        End If

        oReader = oCommand.ExecuteReader
        oRet = New WeiDaoDataSet.AgendaDataTable
        oRet.Load(oReader)

        If bClosed Then m_Conneccion.Close()
        Dim oFr As New MiResultado
        If oRet.Count = 0 Then
            oFr.Futuro = "(No agendado)"
            oFr.Pasado = "(nunca antes)"
            Return oFr
            Exit Function
        End If

        If CDate(oRet.Rows(0).Item("Fecha")) < System.DateTime.Today Then
            oFr.Pasado = CStr(oRet.Rows(0).Item("Fecha"))
            oFr.Futuro = "(No agendado)"
        Else
            oFr.Futuro = CStr(oRet.Rows(0).Item("Fecha"))
            oFr.Futuro = Left(oFr.Futuro, oFr.Futuro.Length - 3) & " hs."

            Dim i As Integer = 1
            Try
                Do While True
                    If CDate(oRet.Rows(i).Item("Fecha")) < System.DateTime.Today Then
                        oFr.Pasado = CStr(oRet.Rows(i).Item("Fecha"))
                        Exit Do
                    Else
                        i = +1
                    End If
                Loop

            Catch ex As IndexOutOfRangeException
                oFr.Pasado = "(nunca antes)    "
            End Try
        End If
        oFr.Pasado = Left(oFr.Pasado, oFr.Pasado.Length - 3) & " hs."
        'FormatDateTime(oFr.Pasado, DateFormat.LongDate)
        Return oFr
    End Function
#Region "Auxiliares"
    Friend Class MiResultado
        Friend Pasado As String
        Friend Futuro As String
    End Class

#End Region
End Class
